/**
 * 文章service
 */
const { validationResult } = require('express-validator');
const { CODE_OK, CODE_BAD_REQUEST, CODE_FORBIDDEN } = require('@root/config/index');
const { poolPromise } = require('@root/db/index');
const { validateParams } = require('@root/utils/peak-tools.js');

/**
 * @description 获取全部文章列表逻辑
 */
async function selectArticleList(req, res, next) {
  if (validateParams({ req, res, validationResult })) return;

  try {
    const { user_id = '' } = req.auth || {};
    const {
      currentPage = 1,
      pageSize = 10,
      status,
      searchKey = '',
      tag_ids = '',
      cate_id = '',
    } = req.query;
    const placeholders_tag_ids = tag_ids
      .split(',')
      .map(() => '?')
      .join(','); // tag占位符

    // IDEA: 查出评论数量,待研究
    const select_article_all_sql = `
select  a.*,
        JSON_OBJECT('cate_id', c.cate_id, 'cate_name', c.cate_name) as category,
        IF(count(t.tag_id) != 0,json_arrayagg(json_object('tag_id', t.tag_id, 'tag_name', t.tag_name)),JSON_ARRAY()) as tags,
        ifnull(a_l.like_count, 0) as like_count,
        ifnull(a_l.user_liked, 0)  as user_liked,
        if(a_c.comment_count is null, 0, a_c.comment_count) as comment_count
from article a
        left join category c ON a.cate_id = c.cate_id
        left join article_tags a_t on a.article_id = a_t.article_id
        left join tags t on t.tag_id = a_t.tag_id
        left join (
          SELECT article_id, MAX(user_id = ?) user_liked, count(*) AS like_count
          FROM article_likes
          GROUP BY article_id
        ) a_l ON a.article_id = a_l.article_id
        left join (
          select article_id, count(*) as comment_count
          from article_comment
          group by article_id
        ) a_c on a.article_id = a_c.article_id
where a.article_id in (select a.article_id
                        from article a
                                left join article_tags a_t on a.article_id = a_t.article_id
                                left join tags t on t.tag_id = a_t.tag_id
                        where a.status = ?
                          and a.article_title like concat('%', ?, '%')
                          and (nullif(?, '') is null or t.tag_id in (${placeholders_tag_ids}))
                          and if(? = '', true, a.cate_id = ?)
                        group by a.article_id)
group by a.article_id
order by a.create_time desc`;

    // 当 tag_ids 为 null|"" 时  (nullif(?, '') is null  为true,  为true不会走 t.tag_id in (${placeholders_tag_ids})
    // nullif(expr1,expr2，则)
    // ■ 如果 expr1 等于 expr2，则 NULLIF 返回 NULL。
    // ■ 如果 expr1 不等于 expr2，则 NULLIF 返回 expr1

    // 参数
    const parameters = [
      Number(user_id),
      status,
      searchKey,
      tag_ids,
      ...tag_ids.split(',').map(Number),
      cate_id,
      cate_id,
    ];
    const [list_all] = await poolPromise.execute(select_article_all_sql, parameters);
    const total = list_all.length;
    if (!total) {
      return res.status(CODE_OK).json({
        code: CODE_OK,
        msg: '暂无数据',
        total,
        currentPage,
        pageSize,
        data: [],
      });
    }
    const startIndex = (currentPage - 1) * pageSize;
    const select_article_sql = `${select_article_all_sql} limit ?,?`;
    const [list] = await poolPromise.execute(select_article_sql, [
      ...parameters,
      String(startIndex),
      String(pageSize),
    ]);
    res.status(CODE_OK).json({
      code: CODE_OK,
      msg: '获取文章成功',
      total,
      currentPage,
      pageSize,
      data: list,
    });
  } catch (error) {
    next(error);
  }
}

/**
 * @description 获取个人文章列表逻辑
 */
async function selectSelfArticleList(req, res, next) {
  if (validateParams({ req, res, validationResult })) return;
  try {
    const { user_id } = req.auth || {};
    if (!user_id) return res.status(401).json({ code: 401, msg: '请先登录' });
    const {
      currentPage = 1,
      pageSize = 10,
      status,
      searchKey = '',
      tag_ids = '',
      cate_id = '',
    } = req.query;
    const placeholders_tag_ids = tag_ids
      .split(',')
      .map(() => '?')
      .join(','); // tag占位符

    const select_article_all_sql = `
select  a.*,
        JSON_OBJECT('cate_id', c.cate_id, 'cate_name', c.cate_name) as category,
        IF(count(t.tag_id) != 0,json_arrayagg(json_object('tag_id', t.tag_id, 'tag_name', t.tag_name)),JSON_ARRAY()) as tags,
        ifnull(a_l.like_count, 0) as like_count,
        ifnull(a_l.user_liked, 0)  as user_liked,
        if(a_c.comment_count is null, 0, a_c.comment_count) as comment_count
from article a
        left join category c ON a.cate_id = c.cate_id
        left join article_tags a_t on a.article_id = a_t.article_id
        left join tags t on t.tag_id = a_t.tag_id
        left join (
          SELECT article_id, MAX(user_id = ?) user_liked, count(*) AS like_count
          FROM article_likes
          GROUP BY article_id
        ) a_l ON a.article_id = a_l.article_id
        left join (
          select article_id, count(*) as comment_count
          from article_comment
          group by article_id
        ) a_c on a.article_id = a_c.article_id
where a.article_id in (select a.article_id
                        from article a
                                left join article_tags a_t on a.article_id = a_t.article_id
                                left join tags t on t.tag_id = a_t.tag_id
                        where a.status = ?
                          and a.article_title like concat('%', ?, '%')
                          and (nullif(?, '') is null or t.tag_id in (${placeholders_tag_ids}))
                          and if(? = '', true, a.cate_id = ?)
                          and a.create_id = ?
                        group by a.article_id)
group by a.article_id
order by a.create_time desc`;

    // 当 tag_ids 为 null|"" 时  (nullif(?, '') is null  为true,  为true不会走 t.tag_id in (${placeholders_tag_ids})
    // nullif(expr1,expr2，则)
    // ■ 如果 expr1 等于 expr2，则 NULLIF 返回 NULL。
    // ■ 如果 expr1 不等于 expr2，则 NULLIF 返回 expr1

    // 参数
    const parameters = [
      Number(user_id),
      status,
      searchKey,
      tag_ids,
      ...tag_ids.split(',').map(Number),
      cate_id,
      cate_id,
      user_id,
    ];
    const [list_all] = await poolPromise.execute(select_article_all_sql, parameters);
    const total = list_all.length;
    if (!total) {
      return res.status(CODE_OK).json({
        code: CODE_OK,
        msg: '暂无数据',
        total,
        currentPage,
        pageSize,
        data: [],
      });
    }
    const startIndex = (currentPage - 1) * pageSize;
    const select_article_sql = `${select_article_all_sql} limit ?,?`;
    const [list] = await poolPromise.execute(select_article_sql, [
      ...parameters,
      String(startIndex),
      String(pageSize),
    ]);
    res.status(CODE_OK).json({
      code: CODE_OK,
      msg: '获取文章成功',
      total,
      currentPage,
      pageSize,
      data: list,
    });
  } catch (error) {
    next(error);
  }
}

/**
 * @description 获取单个文章
 */
async function selectArticle(req, res, next) {
  try {
    if (validateParams({ req, res, validationResult })) return;
    const { user_id = '' } = req.auth || {};
    const { article_id } = req.params;
    const select_article_sql = `
    select
      a.*,
      JSON_OBJECT('cate_id', c.cate_id, 'cate_name', c.cate_name) as category,
      case when count(t.tag_id) != 0 then json_arrayagg(json_object('tag_id',t.tag_id,'tag_name',t.tag_name)) else JSON_ARRAY() end tags,
      ifnull(a_l.like_count, 0) as like_count,
      ifnull(a_l.user_liked, 0) as user_liked,
      if(a_c.comment_count is null, 0, a_c.comment_count) as comment_count
    from
      article a
      left join category c on a.cate_id = c.cate_id
      left join article_tags a_t on a.article_id = a_t.article_id
      left join tags t on t.tag_id = a_t.tag_id
      left join (select article_id,count(id) like_count,max(user_id=?) user_liked from article_likes group by article_id) a_l on a.article_id = a_l.article_id
      left join (select article_id, count(*) as comment_count from article_comment
          group by article_id) a_c on a.article_id = a_c.article_id
    where a.article_id = ?
    group by a.article_id,a_l.article_id`;
    let [list] = await poolPromise.query(select_article_sql, [user_id, article_id]);
    if (!list.length) {
      return res.status(CODE_OK).json({
        code: CODE_OK,
        msg: '文章不存在',
        data: {},
      });
    }
    res.status(CODE_OK).json({
      code: CODE_OK,
      msg: '获取文章成功',
      data: list[0] || {},
    });
  } catch (error) {
    next(error);
  }
}

/**
 * @description 新增文章逻辑
 */
async function insertArticle(req, res, next) {
  if (validateParams({ req, res, validationResult })) return;
  const {
    article_title,
    article_digest,
    article_content,
    article_cover = '',
    article_type,
    comment_status,
    cate_id,
    tag_ids = [], // 文章标签id
  } = req.body;
  const status = 2; // 默认审核中
  const {
    user_id: create_id = '',
    user_id: update_id = '',
    nickname: create_by = '',
    nickname: update_by = '',
  } = req.auth;
  const connection = await poolPromise.getConnection(); // 获取一个连接
  try {
    await connection.beginTransaction();
    const insert_article_sql = `insert into article(article_title,article_digest,article_content,article_cover,cate_id,article_type,status,comment_status,create_by,create_id,update_by,update_id) values(?,?,?,?,?,?,?,?,?,?,?,?)`;
    const [{ affectedRows, insertId: newArticleId }] = await connection.execute(
      insert_article_sql,
      [
        article_title,
        article_digest,
        article_content,
        article_cover,
        cate_id,
        article_type,
        status,
        comment_status,
        create_by,
        create_id,
        update_by,
        update_id,
      ]
    );
    if (!affectedRows) {
      await connection.rollback(); // 回滚事务
      return res.status(CODE_BAD_REQUEST).json({
        code: CODE_BAD_REQUEST,
        msg: '添加失败',
        data: null,
      });
    }
    // 给文章添加标签
    const tag_placeholder = tag_ids.map(() => '(?,?)').join(','); // 标签占位符
    const insert_tag_ids = tag_ids.flatMap((id) => [newArticleId, id]); // 占位对应id
    const insert_article_tag_sql = `insert into article_tags(article_id,tag_id) values ${tag_placeholder}`;
    const [{ affectedRows: affectedRows_insert_article_tag_sql }] = await connection.execute(
      insert_article_tag_sql,
      insert_tag_ids
    );

    if (!affectedRows_insert_article_tag_sql) {
      await connection.rollback(); // 回滚事务
      return res.status(CODE_BAD_REQUEST).json({
        code: CODE_BAD_REQUEST,
        msg: '添加文章分类失败',
        data: null,
      });
    }

    await connection.commit(); // 提交事务
    res.status(CODE_OK).json({
      code: CODE_OK,
      msg: '添加成功',
      data: null,
    });
  } catch (error) {
    await connection.rollback(); // 回滚事务
    next(error);
  } finally {
    connection.release(); // 释放连接
  }
}

/**
 * @description 编辑文章逻辑
 */
async function updateArticle(req, res, next) {
  if (validateParams({ req, res, validationResult })) return;

  const connection = await poolPromise.getConnection();
  try {
    await connection.beginTransaction(); // 开启事务
    const {
      article_id,
      article_title,
      article_digest,
      article_content,
      article_cover = '',
      article_type,
      comment_status,
      cate_id,
      status,
      tag_ids = [], // 文章分类id
    } = req.body;
    const { nickname: update_by = '', user_id: update_id = '' } = req.auth;
    const update_article_sql = `update article set article_title=?,article_digest=?,article_content=?,article_cover=?,cate_id=?,status=?,article_type=?,comment_status=?,update_by=?,update_id=? where article_id=?`;
    const [{ affectedRows }] = await connection.execute(update_article_sql, [
      article_title,
      article_digest,
      article_content,
      article_cover,
      cate_id,
      status,
      article_type,
      comment_status,
      update_by,
      update_id,
      article_id,
    ]);
    if (!affectedRows) {
      connection.rollback();
      return res.status(CODE_BAD_REQUEST).json({
        code: CODE_BAD_REQUEST,
        msg: '修改失败',
        data: null,
      });
    }

    // * 更新文章标签
    // 先删除文章对应的所有标签
    let [{ affectedRows: affectedRows_delete_article_tags_sql }] = await connection.execute(
      `delete from article_tags where article_id = ?`,
      [article_id]
    );
    if (!affectedRows_delete_article_tags_sql) {
      connection.rollback();
      return res.status(CODE_BAD_REQUEST).json({
        code: CODE_BAD_REQUEST,
        msg: '删除文章标签失败',
        data: null,
      });
    }
    // 重新插入
    const insert_tag_ids = tag_ids.map((tag) => [article_id, tag]); // [[article_id, 1], [article_id, 2]]
    const insert_article_tag_sql = `INSERT INTO article_tags (article_id, tag_id) VALUES ?`;
    const [{ affectedRows: affectedRows_insert_article_tags_sql }] = await connection.query(
      insert_article_tag_sql,
      [insert_tag_ids]
    );
    if (!affectedRows_insert_article_tags_sql) {
      await connection.rollback();
      return res.status(CODE_BAD_REQUEST).json({
        code: CODE_BAD_REQUEST,
        msg: '更新文章标签失败',
        data: null,
      });
    }

    await connection.commit();
    res.status(CODE_OK).json({
      code: CODE_OK,
      msg: '修改成功',
      data: null,
    });
  } catch (error) {
    await connection.rollback();
    next(error);
  } finally {
    connection.release();
  }
}

/**
 * @description 删除文章逻辑
 */
async function deleteArticle(req, res, next) {
  try {
    if (validateParams({ req, res, validationResult })) return;
    const { user_id } = req.auth;
    const { ids } = req.body;
    const delete_article_sql = `delete from article where article_id in (${new Array(ids.length)
      .fill('?')
      .join(',')}) and create_id = ?`;
    let [{ affectedRows }] = await poolPromise.execute(delete_article_sql, [...ids, user_id]);
    if (!affectedRows) {
      return res.status(CODE_BAD_REQUEST).json({
        code: CODE_BAD_REQUEST,
        msg: '删除文章失败',
        data: null,
      });
    }
    res.status(CODE_OK).json({
      code: CODE_OK,
      msg: '删除文章成功',
      data: null,
    });
  } catch (error) {
    next(error);
  }
}

/**
 * @description 文章审核
 */
async function articleReview(req, res, next) {
  if (validateParams({ req, res, validationResult })) return;
  const { user_id } = req.auth;
  // XXX: 暂时固定id, 后期用角色的 role_key字段 来判断
  if (user_id !== 26) {
    return res.status(CODE_FORBIDDEN).json({
      code: CODE_FORBIDDEN,
      msg: '没有权限',
      data: null,
    });
  }
  try {
    const { article_id, status, noPass_reason } = req.body;
    const update_article_sql = `update article set status=?,noPass_reason=? where article_id=?`;
    const [{ affectedRows }] = await poolPromise.execute(update_article_sql, [
      status,
      noPass_reason,
      article_id,
    ]);
    if (!affectedRows) {
      return res.status(CODE_BAD_REQUEST).json({
        code: CODE_BAD_REQUEST,
        msg: '审批文章失败',
        data: null,
      });
    }
    res.status(CODE_OK).json({
      code: CODE_OK,
      msg: '审批文章成功',
      data: null,
    });
  } catch (error) {
    next(error);
  }
}

/**
 * 文章-点赞和取消点赞
 */
async function articleLike(req, res, next) {
  if (validateParams({ req, res, validationResult })) return;
  try {
    const { user_id } = req.auth;
    const { article_id } = req.params;
    const select_user_article_like_sql = `select count(*) as count from article_likes where article_id = ? and user_id = ?`;
    let [[{ count }]] = await poolPromise.execute(select_user_article_like_sql, [
      article_id,
      user_id,
    ]); //  先查询判断当前用户是否点过赞

    const select_article_like_sql = `select count(*) like_count from article_likes where article_id = ?`; // 查询当前文章最新的点赞数量

    if (!count) {
      const insert_article_like_sql = `insert into article_likes (article_id, user_id) values (?, ?)`;
      let [{ affectedRows }] = await poolPromise.execute(insert_article_like_sql, [
        article_id,
        user_id,
      ]);
      if (!affectedRows) {
        return res.status(CODE_BAD_REQUEST).json({
          code: CODE_BAD_REQUEST,
          msg: '点赞失败',
        });
      }
      const [[{ like_count }]] = await poolPromise.execute(select_article_like_sql, [article_id]);
      return res.status(CODE_OK).json({
        code: CODE_OK,
        msg: '点赞成功',
        data: {
          like_count,
          user_liked: 1,
        },
      });
    } else {
      const delete_article_like_sql = `delete from article_likes where article_id = ? and user_id = ?`;
      let [{ affectedRows }] = await poolPromise.execute(delete_article_like_sql, [
        article_id,
        user_id,
      ]);
      if (!affectedRows) {
        return res.status(CODE_BAD_REQUEST).json({
          code: CODE_BAD_REQUEST,
          msg: '取消点赞失败',
        });
      }
      const [[{ like_count }]] = await poolPromise.execute(select_article_like_sql, [article_id]);
      return res.status(CODE_OK).json({
        code: CODE_OK,
        msg: '取消点赞成功',
        data: {
          like_count,
          user_liked: 0,
        },
      });
    }
  } catch (error) {
    next(error);
  }
}

module.exports = {
  selectArticleList,
  selectSelfArticleList,
  selectArticle,
  insertArticle,
  updateArticle,
  deleteArticle,
  articleReview,
  articleLike,
};
